﻿using iS3.DAL.Table;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace iS3.DAL.SQLSERVER
{
    public class DataContext_SQLSERVER<TEntity> : IDataContext<TEntity> where TEntity:DGBase,new()
    {
        private string _nameOrConnectionStr;
        private SQLConn _SQLConn;
        private DGEntityStruct _struct=null;
        /// <summary> 
        /// SQLSERVER上下文构造函数
        /// </summary>
        /// <param name="nameOrConnectionStr">连接参数，例如"server ={0}; database = {1}; uid = {2}; pwd = {3};"</param>
        /// <returns></returns>
        public DataContext_SQLSERVER(string nameOrConnectionStr)
        {
            this._nameOrConnectionStr = nameOrConnectionStr;
            this._SQLConn = new SQLConn(nameOrConnectionStr);
        }
        public DataContext_SQLSERVER(string nameOrConnectionStr, DGEntityStruct _struct=null)
        {
            this._nameOrConnectionStr = nameOrConnectionStr;
            this._SQLConn = new SQLConn(nameOrConnectionStr);
            this._struct = _struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : _struct;
        }
        #region  initdatabase
        public bool InitDatabase()
        {
            string sqlcmd = string.Empty;
            try
            {
                Exception expt = null;

                using (SqlConnection sqlconn = new SqlConnection(_SQLConn.getMaster()))
                {
                    sqlconn.Open();
                    sqlcmd = $@"IF Not EXISTS (Select * From [sys].[databases] where name = '{_SQLConn.database}') Create database [{_SQLConn.database}]";
                    using (SqlCommand cmd = new SqlCommand(sqlcmd, sqlconn))
                    {
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (SqlException ex)
                        {
                            expt = new Exception($"{JsonConvert.SerializeObject(ex, Formatting.Indented)}");
                        }
                    }
                }
                return SleepCheckDatabaseExist(expt);
            }
            catch (Exception ex)
            {

                //throw new Exception($"创建数据库时报错,命令{sqlcmd}", ex);
                return true;
            }
        }
        bool SleepCheckDatabaseExist( Exception expt)
        {
            try
            {
                Thread.Sleep(1000);
                if (!CheckDatabaseExist())   //当前没有检测到表格出现
                {
                    throw expt;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                if (ex == null)   //并非报错，正常创建过程
                {
                    return SleepCheckDatabaseExist( ex);
                }
                else
                {
                    throw ex;
                }
            }
        }
        public  bool CheckDatabaseExist()
        {
            try
            {
                using (SqlConnection sqlconn = new SqlConnection(_nameOrConnectionStr))
                {
                    sqlconn.Open();
                    string sqlcmd = $"select * from master.dbo.sysdatabases where name ='{sqlconn.Database}'";
                    bool Exist = true;
                    using (SqlCommand cmd = new SqlCommand(sqlcmd, sqlconn))
                    {
                        SqlDataReader reader = cmd.ExecuteReader();
                        Exist = reader.HasRows;
                        reader.Close();
                    }
                    sqlconn.Close();
                    return Exist;
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"检测数据库是否存在时报错", ex);
            }
        }
        #endregion

        #region     updateTableStruct
        //ALTER TABLE Table_1 ADD address2 varchar(30)             新增
        //ALTER TABLE Table_1 ALTER COLUMN address2 varchar(80);   修改类型
        //EXEC sp_rename 'Table_1.[a]', 'aa', 'COLUMN';            修改列名
        //ALTER TABLE Table_1 DROP COLUMN address                  删除列 
        public bool UpdateTable_add(DGPropertyStruct model)
        {
            string sql = string.Format("ALTER TABLE # ADD {0}", $"[{model.PropertyName}]{iS3DataType.getColumnType(model.SqlType),-20}{(model.IsCanNull ? "NULL" : "NOT NULL"),-20}");
            return UpdateTable(sql);
        }

        public bool BatchUpdateTable_add(List<DGPropertyStruct> models)
        {
            List<string> sqlList = new List<string>();
            models.ForEach(model =>
            {
                string sql = string.Format("ALTER TABLE # ADD {0}", $"[{model.PropertyName}]{iS3DataType.getColumnType(model.SqlType),-20}{(model.IsCanNull ? "NULL" : "NOT NULL"),-20}");
                sqlList.Add(sql);
            });
            
            return UpdateTable(string.Join(";", sqlList));
        }

        public bool UpdateTable_modify(DGPropertyStruct oldOne, DGPropertyStruct newOne)
        {
            //判断
            List<string> sqlList = new List<string>();
            if (newOne.PropertyName != oldOne.PropertyName)
            {
                sqlList.Add(
                    string.Format("EXEC sp_rename '#.[{0}]', '{1}', 'COLUMN'", oldOne.PropertyName, newOne.PropertyName)
                    );
            };
            if (newOne.SqlType != oldOne.SqlType)
            {
                sqlList.Add(
                    string.Format("ALTER TABLE # ALTER COLUMN {0}", $"[{newOne.PropertyName}]{iS3DataType.getColumnType(newOne.SqlType),-20}{(newOne.IsCanNull ? "NULL" : "NOT NULL"),-20}")
                    );
            }
            if (sqlList.Count > 0)
            {
                return UpdateTable(string.Join(";", sqlList));
            }
            else
            {
                return true;
            }
        }

        public bool BatchUpdateTable_modify(List<DGPropertyStruct> oldOnes, List<DGPropertyStruct> newOnes)
        {
            //判断
            List<string> sqlList = new List<string>();
            for (int i = 0; i < newOnes.Count; i++)
            {
                DGPropertyStruct newOne = newOnes[i];
                DGPropertyStruct oldOne = oldOnes[i];
                if (newOne.PropertyName != oldOne.PropertyName)
                {
                    sqlList.Add(
                        string.Format("EXEC sp_rename '#.[{0}]', '{1}', 'COLUMN'", oldOne.PropertyName, newOne.PropertyName)
                        );
                };
                if (newOne.SqlType != oldOne.SqlType)
                {
                    sqlList.Add(
                        string.Format("ALTER TABLE # ALTER COLUMN {0}", $"[{newOne.PropertyName}]{iS3DataType.getColumnType(newOne.SqlType),-20}{(newOne.IsCanNull ? "NULL" : "NOT NULL"),-20}")
                        );
                }
            }
            if (sqlList.Count > 0)
            {
                return UpdateTable(string.Join(";", sqlList));
            }
            else
            {
                return true;
            }
        }

        public bool UpdateTable_delete(DGPropertyStruct model)
        {
            string sql = string.Format("ALTER TABLE # DROP COLUMN {0}", model.PropertyName);
            return UpdateTable(sql);
        }

        public bool BatchUpdateTable_delete(List<DGPropertyStruct> models)
        {
            List<string> sqlList = new List<string>();
            models.ForEach(model =>
            {
                string sql = string.Format("ALTER TABLE # DROP COLUMN {0}", model.PropertyName);
                sqlList.Add(sql);
            });
            return UpdateTable(string.Join(";", sqlList));
        }
        //更新表结构
        //找出当前项目中所有引用到该模型的表格，统一修改更新
        public bool UpdateTable(string sqlcmd)
        {
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;

            string rawTableName = _struct.TableName;
            //找到模型相关的所有表名
            List<string> tableNameList = getAllTableName().Where(x => x.StartsWith(rawTableName)).ToList();
            using (SqlConnection sqlconn = new SqlConnection(_SQLConn.getMaster()))
            {
                sqlconn.Open();
                tableNameList.ForEach(tableName =>
                {
                    string newSqlcmd = sqlcmd.Clone().ToString();
                    newSqlcmd.Replace("#", tableName);
                    using (SqlCommand cmd = new SqlCommand(newSqlcmd, sqlconn))
                    {
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (SqlException ex)
                        {
                            //
                        }
                    }
                });
            }
            return true;
        }
        public List<string> getAllRow(string tableName)
        {
            List<string> res = new List<string>();
            string sql = string.Format("select name,colorder from syscolumns where id=object_id('{0}') order by colorder asc", tableName);
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 设置自增值
                    if (dataTable.Rows.Count > 0)
                    {
                        for (int i = 0; i < dataTable.Rows.Count; i++)
                        {
                            res.Add(dataTable.Rows[i][0].ToString());
                        }
                    }
                }
            }
            return res;
        }
        public List<string> getAllTableName()
        {
            List<string> res = new List<string>();
            string sql = "SELECT NAME FROM SYSOBJECTS WHERE TYPE='U'";
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 设置自增值
                    if (dataTable.Rows.Count > 0)
                    {
                        for (int i = 0; i < dataTable.Rows.Count; i++)
                        {
                            res.Add(dataTable.Rows[i][0].ToString());
                        }
                    }
                }
            }
            return res;
        }
        #endregion 

        #region initTable
        public bool InitTableDone(string tableName, DGEntityStruct _struct)
        {
            string createSQL = string.Empty;

            //since tablename maybe self define


            List<string> columnList = new List<string>();
            foreach (DGPropertyStruct dps in _struct.StructList)
            {
                if (dps.IsPrimaryKey)
                {
                    //modify: move the autoIncreament
                    //if (dps.IsAutoIncrement)
                    //{
                    //    columnList.Add($"[{dps.PropertyName}]{iS3DataType.getColumnType(dps.SqlType),-20}{(dps.IsCanNull ? "NULL" : "NOT NULL"),-20}{" primary  key",-20}{"identity(1,1)",-20}");
                    //}
                    //else
                    //{
                    columnList.Add($"[{dps.PropertyName}]{iS3DataType.getColumnType(dps.SqlType),-20}{(dps.IsCanNull ? "NULL" : "NOT NULL"),-20}{"primary key",-20}");
                    //}

                }
                else
                {
                    columnList.Add($"[{dps.PropertyName}]{iS3DataType.getColumnType(dps.SqlType),-20}{(dps.IsCanNull ? "NULL" : "NOT NULL"),-20}");
                }
            }
            createSQL = string.Format("CREATE TABLE [dbo].[{0}]({1})", tableName, string.Join(",\r\n", columnList));

            createSQL = $@"IF NOT EXISTS (select * from dbo.sysobjects where xtype='U' and Name = '{tableName}') BEGIN {createSQL} END";
            using (SqlConnection sqlconn = new SqlConnection(_nameOrConnectionStr))
            {
                sqlconn.Open();
                using (SqlCommand cmd = new SqlCommand(createSQL, sqlconn))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                        sqlconn.Close();
                        return true;
                    }
                    catch (SqlException ex)
                    {
                        throw ex;
                    }
                }
            }
        }
        public bool InitTable(ExternalStorageConfig config = null)
        {
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;

            string useTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;   //自定义的表格前缀或者是默认的

            if ((config != null) && (config.SubTableRule != "0"))   //若分表存储，则不需要初始表结构
            {
                return true;
            }
            return InitTableDone(useTableName, _struct);
        }
       
        #endregion
        public int CountDone(string where,string tableName)
        {
            string sql = where == null ? string.Format("select count(*) from [{0}]", tableName) :
                string.Format("select count(*) from [{0}] where ({1})", tableName, where);
            int count = 0;
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 设置自增值
                    if (dataTable.Rows.Count > 0)
                    {
                        count = int.Parse(dataTable.Rows[0][0].ToString());
                    }
                }
            }
            return count;
        }
        public int Count(string where=null,ExternalStorageConfig config = null)
        {
            // select count(*) from [atest3].[dbo].[Test_ModelA]
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;

            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn,rawTableName);
            List<string> tableNames = engine.getAllExtTableName();        //获取分库状况下的所有表名

            int count = 0;
            tableNames.ForEach(useTabelName =>
            {
                count += CountDone(where, useTabelName);
            });
            return count;
        }

        #region create
        /// <summary>
        /// 返回插入的字符串
        /// </summary>
        /// <returns>结果如:insert into tableName(id,Name,Value) values(@id,@Name,@Value)</returns>
        /// 
        public TEntity CreateDone(TEntity model, string useTableName)
        {
            StringBuilder _TempSql = new StringBuilder();
            StringBuilder _TempSql2 = new StringBuilder();

            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;


            _TempSql.Append(string.Format("insert into {0} (", useTableName));
            _TempSql2.Append(") Values(");

            foreach (DGPropertyStruct dps in _struct.StructList)
            {
                //modify: move IsAutoIncrement
                //if (dps.IsAutoIncrement)
                //{
                //    continue;    // 跳过自增列
                //}
                _TempSql.Append(string.Format("{0},", DBHelp.Keyword(dps.PropertyName)));

                object _r = model.GetValue(dps.PropertyName);

                string value = _r == null ? null : _r.ToString();

                if ((value == null) || (value.Length == 0))
                {
                    value = "NULL";

                }
                else
                {
                    if (value.IndexOf('\'') != -1)
                    {
                        value = value.Replace("\'", "\''");
                    }

                    value = "'" + value + "'";
                }

                _TempSql2.Append(string.Format("{0},", value));
            }
            string sql = _TempSql.ToString().TrimEnd(',') + _TempSql2.ToString().TrimEnd(',') + ")";

            //modify  id can be know out of database
            DGPropertyStruct pDPS = _struct.PrimaryKey();
            if (pDPS != null)
            {
                if (pDPS.IsAutoIncrement)
                {
                    //sql = string.Format("{0} ;select  @@IDENTITY AS {1}", sql, DBHelp.Keyword(pDPS.PropertyName));
                    sql = string.Format("{0} ", sql);
                }
            }
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 设置自增值
                    //if (dataTable.Rows.Count > 0)
                    //{
                    //    try
                    //    {
                    //        string _outValue = dataTable.Rows[0][pDPS.PropertyName].ToString();

                    //        model.SetValue(pDPS.PropertyName, _outValue);

                    //    }
                    //    catch (Exception ex)
                    //    {
                    //        throw ex;
                    //    }

                    //}


                    return model;
                }
            }

        }
        public TEntity Create(TEntity model, ExternalStorageConfig config = null)
        {
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;

            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            string useTableName = engine.getExtTableName<TEntity>(model);        //获取分库状况下的所有表名

            if (!engine.checkIfExist(useTableName))
            {
                InitTableDone(useTableName, _struct);
                //向Record写入信息
                ExternalStorageRecord record = engine.recordCreatingTable(useTableName);
                record.ID = genId();
                record.SYS_DataTime = DateTime.Now;
                record.SYS_DataState = true;
                record.SYS_DataSet = "";
                CreateDoneOwnEntity<ExternalStorageRecord>(record, "Project_ExternalStorageRecord");
            }

            return CreateDone(model, useTableName);
        }
        public QEntity CreateDoneOwnEntity<QEntity>(QEntity model, string useTableName) where QEntity : DGBase, new()
        {
            StringBuilder _TempSql = new StringBuilder();
            StringBuilder _TempSql2 = new StringBuilder();

            DGEntityStruct _struct = DGEntityManager.GetInstance().GetStruct(typeof(QEntity));


            _TempSql.Append(string.Format("insert into {0} (", useTableName));
            _TempSql2.Append(") Values(");

            foreach (DGPropertyStruct dps in _struct.StructList)
            {
                _TempSql.Append(string.Format("{0},", DBHelp.Keyword(dps.PropertyName)));

                object _r = model.GetValue(dps.PropertyName);

                string value = _r == null ? null : _r.ToString();
                if ((value == null) || (value.Length == 0))
                {
                    value = "NULL";

                }
                else
                {
                    if (value.IndexOf('\'') != -1)
                    {
                        value = value.Replace("\'", "\''");
                    }

                    value = "'" + value + "'";
                }

                _TempSql2.Append(string.Format("{0},", value));
            }
            string sql = _TempSql.ToString().TrimEnd(',') + _TempSql2.ToString().TrimEnd(',') + ")";

            //modify  id can be know out of database
            DGPropertyStruct pDPS = _struct.PrimaryKey();
            if (pDPS != null)
            {
                if (pDPS.IsAutoIncrement)
                {
                    //sql = string.Format("{0} ;select  @@IDENTITY AS {1}", sql, DBHelp.Keyword(pDPS.PropertyName));
                    sql = string.Format("{0} ", sql);
                }
            }
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();
                    return model;
                }
            }

        }

        public List<TEntity> BatchCreateDone(List<TEntity> models, string useTableName,DGEntityStruct _struct)
        {
            DataTable myDataTable = (Activator.CreateInstance(typeof(TEntity)) as DGBase).TransformToDataTable(models, _struct);

            //DataTable myDataTable = (Activator.CreateInstance(typeof(TEntity)) as DGBase).TransformToDataTable(models);
            //DataTable myDataTable = DGEntityStruct.TransformToDataTable(typeof(TEntity), models);
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(_nameOrConnectionStr))
            {
                sqlBulkCopy.DestinationTableName = useTableName;

                sqlBulkCopy.WriteToServer(myDataTable);
            }
            //更新插入的自增id
            //return queryTop(models.Count);
            //modify
            return models;
        }

        public List<TEntity> BatchCreate(List<TEntity> models, ExternalStorageConfig config = null)
        {
            List<TEntity> res = new List<TEntity>();
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;

            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            Dictionary<string,List<TEntity>> dict = engine.getExtTableName<TEntity>(models);        //获取分库状况下的所有表名

            //获取元数据定义，根据数据库实际顺序调整
            DGEntityStruct _copyStruct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            List<DGPropertyStruct> dps = _copyStruct.StructList;
            _copyStruct.StructList = new List<DGPropertyStruct>();

            ////获取表结构，更新列顺序
            bool once = false;


            foreach (string tableName in dict.Keys)
            {
                if (!engine.checkIfExist(tableName))
                {
                    _copyStruct.StructList = dps;
                    InitTableDone(tableName, _copyStruct);
                    ExternalStorageRecord record = engine.recordCreatingTable(tableName);
                    record.ID = genId();
                    record.SYS_DataTime = DateTime.Now;
                    record.SYS_DataState = true;
                    record.SYS_DataSet = "";
                    CreateDoneOwnEntity<ExternalStorageRecord>(record, "Project_ExternalStorageRecord");
                }
                else
                {
                    if (!once)
                    {
                        //根据数据库表结构修改元模型顺序
                        List<string> tableRowList = getAllRow(tableName);
                        tableRowList.ForEach(x =>
                        {
                            _copyStruct.StructList.Add(dps.Where(y => y.PropertyName == x).FirstOrDefault());
                        });
                        once = true;
                    }
                }


                res.AddRange(BatchCreateDone(dict[tableName], tableName, _copyStruct));
            }
            return res;
        }
        #endregion

        #region  Retrieve
        public TEntity RetrieveByIDDone(long id, string useTableName)
        {
            TEntity res =null;
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            string sql = string.Format("select * from {0} where ([ID]={1})", useTableName, id);
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 设置自增值
                    if (dataTable.Rows.Count > 0)
                    {
                        res = new TEntity();
                        foreach (DGPropertyStruct dps in _struct.StructList)
                        {
                            try
                            {
                                string _outValue = dataTable.Rows[0][dps.PropertyName].ToString();
                                res.SetValue(dps.PropertyName, _outValue);
                            }
                            catch (Exception ex)
                            {
                                res.SetValue(dps.PropertyName, null);
                            }
                        }
                    }
                    return res;
                }
            }
        }
        public TEntity Retrieve(long id, ExternalStorageConfig config = null)
        {
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;

            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            List<string> tableNames = engine.getAllExtTableName();        //获取分库状况下的所有表名

            for (int i = 0; i < tableNames.Count; i++)
            {
                TEntity _res = RetrieveByIDDone(id, tableNames[i]);
                if (_res != null)
                {
                    return _res;
                }
            }
            return null;
        }
        public List<TEntity> RetrieveList(string dataset = null, ExternalStorageConfig config = null)
        {
            if (typeof(TEntity).IsSubclassOf(typeof(DGEntity)))
            {
                string whereSQL = null;
                if ((dataset != null))
                {
                    whereSQL = string.Format("(([SYS_DataState]='True') and ([SYS_DataSet]='{0}'))", dataset);
                }
                else
                {
                    whereSQL = string.Format("([SYS_DataState]='True')", dataset);
                }
                return Query(whereSQL,null,config);
            }
            else
            {
                return Query(null, null, config);
            }
        }


        public List<TEntity> Query(string where=null,string order=null, ExternalStorageConfig config = null)
        {
            List<TEntity> result = new List<TEntity>();
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;

            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            List<string> tableNames = engine.getAllExtTableNameByParam(where);        //获取分库状况下的所有表名

            string str = "";

            if (tableNames.Count == 1)
            {
                //单表查询
                str = tableNames[0];
            }
            else
            {
                //多表查询
                List<string> list = new List<string>();
                //获取初步筛选的数据
                tableNames.ForEach(useTableName =>
                {
                    if (where == null)
                    {
                        list.Add(string.Format("select * from [{0}] ", useTableName));
                    }
                    else
                    {
                        list.Add(string.Format("select * from [{0}] where {1} ", useTableName, where));
                    }

                });
                str = string.Join(" union all ", list);
                str = string.Format("({0})A", str);
            }


            return QueryDone(where, order, str);
        }
        #region   //分页查询优化
        public List<TEntity> QueryByPageDone_OneTable(string useTableName, int start,int end, string order, string where = null)
        {
            List<TEntity> res = new List<TEntity>();
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            // 

            string sql = where == null ?
                string.Format("SELECT* FROM(select ROW_NUMBER() OVER (ORDER BY {0} ) as RowNumber,* from {1})k where RowNumber BETWEEN {2} and {3}",
                order, useTableName, start, end) :
                string.Format("SELECT* FROM(select ROW_NUMBER() OVER (ORDER BY {0} ) as RowNumber,*from {1} where {2})k where RowNumber BETWEEN {3} and {4}",
                order, useTableName, where, start, end);

            //SELECT* FROM(select ROW_NUMBER() OVER (ORDER BY ID asc ) as RowNumber,*from[Geology_Borehole])k where RowNumber BETWEEN 1 and 2
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 
                    return Activator.CreateInstance<TEntity>().TransformToEntity<TEntity>(dataTable, _struct);
                }
            }
        }
        public List<TEntity> QueryByPage_Opt(int pageSize, int currentPage, string order, string where = null, ExternalStorageConfig config = null)
        {
            List<TEntity> result = new List<TEntity>();
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;

            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            List<string> tableNames = engine.getAllExtTableNameByParam(where);        //获取分库状况下的所有表名

            int start = (currentPage - 1) * pageSize + 1;
            int end = currentPage * pageSize;
            int tot = 1;

            List<TEntity> resultData = new List<TEntity>();
            for (int i = 0; i < tableNames.Count; i++)
            {
                if (start <= end)
                {
                    int count = CountDone(where, tableNames[i]);
                    int nowEnd = tot + count-1;
                    if (!(((tot < start) && (nowEnd < start)) || ((tot > end) && (nowEnd > end))))
                    {
                        //在区间内
                        if ((nowEnd >= start) && (nowEnd <= end))
                        {
                            int _start = start-start+1;
                            int _end = nowEnd-start+1;
                            resultData.AddRange(QueryByPageDone_OneTable(tableNames[i], _start, _end, order, where));
                            start = nowEnd + 1;
                        }
                        else
                        {
                            int _start = start - start + 1;
                            int _end = end - start + 1;
                            resultData.AddRange(QueryByPageDone_OneTable(tableNames[i], _start, _end, order, where));
                            start = end + 1;
                        }
                    }
                    tot = nowEnd+1;
                };
;
            }

            return resultData;
        }
        #endregion
        public List<TEntity> QueryByPageDone(string useTableName,int pageSize, int currentPage, string order, string where = null)
        {
            List<TEntity> res = new List<TEntity>();
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            // 
            int start = (currentPage - 1) * pageSize + 1;
            int end = currentPage * pageSize;

            string sql = where == null ?
                string.Format("SELECT* FROM(select ROW_NUMBER() OVER (ORDER BY {0} ) as RowNumber,* from {1})k where RowNumber BETWEEN {2} and {3}",
                order, useTableName, start, end) :
                string.Format("SELECT* FROM(select ROW_NUMBER() OVER (ORDER BY {0} ) as RowNumber,*from {1} where {2})k where RowNumber BETWEEN {3} and {4}",
                order, useTableName, where, start, end);

            //SELECT* FROM(select ROW_NUMBER() OVER (ORDER BY ID asc ) as RowNumber,*from[Geology_Borehole])k where RowNumber BETWEEN 1 and 2
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 
                    return Activator.CreateInstance<TEntity>().TransformToEntity<TEntity>(dataTable, _struct);
                }
            }
        }
        public List<TEntity> QueryByPage(int pageSize,int currentPage, string order,string where = null, ExternalStorageConfig config = null)
        {
            List<TEntity> result = new List<TEntity>();
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;

            if ((config != null) && (config.SubTableRule == "1"))
            {
                return QueryByPage_Opt(pageSize, currentPage, order, where, config);
                //var configParam = Newtonsoft.Json.JsonConvert.DeserializeObject<ExternalStorage_TimeSplitParam>(config.SubTableParam);
                //if ((where==null)|| (where.IndexOf(configParam.TimeProperty) == -1)){
                //    //优化处理
                //    return QueryByPage_Opt(pageSize, currentPage, order, where, config);
                //}
            }

            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            List<string> tableNames = engine.getAllExtTableNameByParam(where);        //获取分库状况下的所有表名


            //string useTableNameSQL = "(select * from[Structure_UMAR_50236e449dc946cda09359f33014ce86] union all select * from[Structure_UMAR_6a07bef8ef624970862ab003f4377aa3])A";
            List<string> list = new List<string>();
            //获取初步筛选的数据
            tableNames.ForEach(useTableName =>
            {
                if (where == null)
                {
                    list.Add(string.Format("select * from [{0}] ", useTableName));
                }
                else
                {
                    list.Add(string.Format("select * from [{0}] where {1} ", useTableName, where));
                }

            });

            if (list.Count == 0)
            {
                return new List<TEntity>();
            }
            string str = string.Join(" union all ", list);
            str = string.Format("({0})A", str);

            return QueryByPageDone(str, pageSize, currentPage, order, where);
        }
       
    
        //条件查询   时间相关  top查询  分页查询
        //排序查询  分表字段  非分表字段
        public List<TEntity> QueryDone(string whereSQL,string orderSQL, string useTableName)
        {
            List<TEntity> res = new List<TEntity>();
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;


            string sql = whereSQL == null ? string.Format("select * from {0}", _struct.TableName) : string.Format("select * from {0} where {1}", useTableName, whereSQL);
            sql = orderSQL == null ? sql : string.Format("{0} order by {1}", sql, orderSQL);
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 
                    return Activator.CreateInstance<TEntity>().TransformToEntity<TEntity>(dataTable, _struct);
                }
            }
        }

        #endregion

        #region delete
        public int BatchDeleteDone(List<long> ids, string useTableName)
        {
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            string sql = string.Format("delete  from {0} where ([ID] in ({1}))", useTableName, string.Join(",", ids));
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        int res = cmd.ExecuteNonQuery();
                        conn.Close();
                        return res;
                    }
                    catch (SqlException ex)
                    {
                        throw ex;
                    }
                }
            }
        }
        public int BatchDelete(List<long> ids, ExternalStorageConfig config = null)
        {
            int result = 0;
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;
            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            List<string> tableNames = engine.getAllExtTableName();        //获取分库状况下的所有表名

            tableNames.ForEach(useTableName =>
            {
                result += BatchDeleteDone(ids, useTableName);
            });
            return result;
        }

        public int DeleteDone(long id, string useTableName)
        {
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
           // string sql = string.Format("delete  from {0} where ([ID]={1})", useTableName, id);
            string sql = string.Format("UPDATE {0} set SYS_DataState='false' where ([ID]={1})", useTableName, id);

            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        int res = cmd.ExecuteNonQuery();
                        conn.Close();
                        return res;
                    }
                    catch (SqlException ex)
                    {
                        throw ex;
                    }
                }
            }
        }
        public int Delete(long id, ExternalStorageConfig config = null)
        {
            int result = 0;
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;
            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            List<string> tableNames = engine.getAllExtTableName();        //获取分库状况下的所有表名

            tableNames.ForEach(useTableName =>
            {
                result += DeleteDone(id, useTableName);
            });
            return result;
        }


        public int  DeleteRange(string where = null, ExternalStorageConfig config = null)
        {
            int result = 0;
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;
            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            List<string> tableNames = engine.getAllExtTableName();        //获取分库状况下的所有表名

            tableNames.ForEach(useTableName =>
            {
                result += DeleteRangeDone(where, useTableName);
            });
            return result;
        }
        public int DeleteRangeDone(string where, string useTableName)
        {
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            // string sql = string.Format("delete  from {0} where ([ID]={1})", useTableName, id);
            string sql = where == null ? string.Format("UPDATE {0} set SYS_DataState='false'", _struct.TableName) : string.Format("UPDATE {0} set SYS_DataState='false' where {1}", useTableName, where);
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        int res = cmd.ExecuteNonQuery();
                        conn.Close();
                        return res;
                    }
                    catch (SqlException ex)
                    {
                        throw ex;
                    }
                }
            }
        }
        #endregion

        #region update
        public TEntity UpdateDone(TEntity model, string useTableName)
        {
            StringBuilder _TempSql = new StringBuilder();
            StringBuilder _TempSql2 = new StringBuilder();

            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            _TempSql.Append(string.Format("Update {0} set ", useTableName));

            List<string> equalSQL = new List<string>();
            foreach (DGPropertyStruct dps in _struct.StructList)
            {
                //modify
                //if (dps.IsAutoIncrement)
                //{
                //    continue;    // 跳过自增列
                //}

                object getV = model.GetValue(dps.PropertyName);
                string value = getV == null ? null : getV.ToString();
                if ((value == null) || (value.Length == 0))
                {
                    value = "NULL";

                }
                else
                {
                    if (value.IndexOf('\'') != -1)
                    {
                        value = value.Replace("\'", "\''");
                    }

                    value = "'" + value + "'";
                }

                _TempSql.Append(string.Format("[{0}]={1},", dps.PropertyName, value));
            }
            string sql = _TempSql.ToString().TrimEnd(',') + string.Format(" where([ID]={0})", model.ID);

            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();
                    return model;
                }
            }
        }
        public TEntity Update(TEntity model, ExternalStorageConfig config = null)
        {
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;
            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            List<string> tableNames = engine.getAllExtTableName();        //获取分库状况下的所有表名

            tableNames.ForEach(useTableName =>
            {
                UpdateDone(model, useTableName);
            });
            return model;
        }
        public TEntity Update(TEntity model, string propertyName, ExternalStorageConfig config = null)
        {
            return Update(model, new List<string>() { propertyName }, config);
        }
        public TEntity UpdateDone(TEntity model, List<string> propertyNames, string useTableName)
        {
            StringBuilder _TempSql = new StringBuilder();
            StringBuilder _TempSql2 = new StringBuilder();

            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            _TempSql.Append(string.Format("Update {0} set ", useTableName));

            List<string> equalSQL = new List<string>();
            foreach (DGPropertyStruct dps in _struct.StructList)
            {
                if (!propertyNames.Contains(dps.PropertyName))
                {
                    continue;
                }
                object getV = model.GetValue(dps.PropertyName);
                string value = getV == null ? null : getV.ToString();
                if ((value == null) || (value.Length == 0))
                {
                    value = "NULL";

                }
                else
                {
                    if (value.IndexOf('\'') != -1)
                    {
                        value = value.Replace("\'", "\''");
                    }

                    value = "'" + value + "'";
                }

                _TempSql.Append(string.Format("[{0}]={1},", dps.PropertyName, value));
            }
            string sql = _TempSql.ToString().TrimEnd(',') + string.Format(" where([ID]={0})", model.ID);

            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();
                    return model;
                }
            }
        }
        public TEntity Update(TEntity model,List<string> propertyNames, ExternalStorageConfig config = null)
        {
            string rawTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;
            IExternalStorageEngine engine = ExternalStorageEngineFactory.getInstance(config, _SQLConn, rawTableName);
            List<string> tableNames = engine.getAllExtTableName();        //获取分库状况下的所有表名

            tableNames.ForEach(useTableName =>
            {
                UpdateDone(model, propertyNames,useTableName);
            });
            return model;
        }
       
        public List<TEntity> BatchUpdate(List<TEntity> models, ExternalStorageConfig config = null)
        {
            models.ForEach(
                x =>
                {
                    Update(x, config);
                });
            return models;
        }
        #endregion 

        #region methods
        public List<TEntity> queryTop(int topNum)
        {
            List<TEntity> res = new List<TEntity>();
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;
            string sql = string.Format("select top {0} * from {1} order by [ID] desc", topNum, _struct.TableName);
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 
                    return Activator.CreateInstance<TEntity>().TransformToEntity<TEntity>(dataTable, _struct);
                    //return DGEntityStruct.TransformToEntity<TEntity>(dataTable);
                }
            }
        }
        public List<TEntity> queryWhere(string whereSQL,string orderSQL=null, ExternalStorageConfig config = null)
        {
            List<TEntity> res = new List<TEntity>();
            DGEntityStruct _struct = this._struct == null ? DGEntityManager.GetInstance().GetStruct(typeof(TEntity)) : this._struct;

            string useTableName = config == null ? _struct.TableName : config.TableNameOrPrefix;

            string sql = whereSQL==null? string.Format("select * from {0}", _struct.TableName): string.Format("select * from {0} where {1}", useTableName, whereSQL);
            sql = orderSQL == null ? sql : string.Format("{0} order by {1}", sql, orderSQL);
            using (SqlConnection conn = new SqlConnection(_nameOrConnectionStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(sql, conn))
                {
                    sqlCommand.CommandTimeout = 300;
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    DataTable dataTable = new DataTable();
                    dataTable.Load(sqlDataReader);
                    sqlDataReader.Close();

                    // 
                    return Activator.CreateInstance<TEntity>().TransformToEntity<TEntity>(dataTable,_struct);
                    //return DGEntityStruct.TransformToEntity<TEntity>(dataTable);
                }
            }
        }
        #endregion
        public long genId()
        {
            return Snowflake.Instance().GetId();
        }
        public List<long> genIdList(int num)
        {
            List<long> res = new List<long>();

            for (int i = 0; i < num; i++)
            {
                long one = Snowflake.Instance().GetId();
                while (res.IndexOf(one) > -1)
                {
                    one = Snowflake.Instance().GetId();
                }
                res.Add(one);
            }
            return res;
        }


    }
}
